package cn.source.zk.mapper;

import cn.source.zk.domain.entity.Substation;
import cn.source.zk.domain.entity.SubstationStatVO;
import cn.source.zk.domain.entity.SubstationTypeStatVO;
import cn.source.zk.domain.vo.SubstationConnectionPointVO;
import com.alibaba.fastjson2.JSONObject;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

@Mapper
public interface ZkSubstationMapper {

    /**
     * 查询变电站分页列表
     * @param substation
     * @return
     */
    List<Substation> selectListPage(Substation substation);

    List<JSONObject> countSubstationsByArea();

    List<JSONObject> countDevicesBySubstationType();

    List<JSONObject> countSubstationsByType();

    @SelectProvider(type = SqlProvider.class, method = "countByTimeRange")
    long countSubstationsThisMonth(String timeRange);

    @SelectProvider(type = SqlProvider.class, method = "countByDeviceTimeRange")
    long countDevicesThisMonth(String timeRange);

    @Select("SELECT ancestor_names AS name, COUNT(*) AS value " +
            "FROM zk_connection_point_info " +
            "WHERE ancestor_names IS NOT NULL AND ancestor_names != '' " +
            "GROUP BY ancestor_names")
    List<Map<String,Object>> countByRegion();



    // 按年
    @Select({
            "SELECT",
            "  CAST(YEAR(i.create_time) AS CHAR) AS timeKey,",
            "  i.type,",
            "  d.dict_label AS dictLabel,",
            "  COUNT(*) AS count",
            "FROM zk_substation_info i",
            "LEFT JOIN sys_dict_data d ON d.dict_value = i.type AND d.dict_type = 'substation_type'",
            "WHERE i.create_time IS NOT NULL AND i.type IS NOT NULL",
            "GROUP BY CAST(YEAR(i.create_time) AS CHAR), i.type, d.dict_label",
            "ORDER BY timeKey"
    })
    List<SubstationStatVO> statByYear();

    // 按季度
    @Select({
            "SELECT",
            "  CONCAT(YEAR(i.create_time), '-Q', QUARTER(i.create_time)) AS timeKey,",
            "  i.type,",
            "  d.dict_label AS dictLabel,",
            "  COUNT(*) AS count",
            "FROM zk_substation_info i",
            "LEFT JOIN sys_dict_data d ON d.dict_value = i.type AND d.dict_type = 'substation_type'",
            "WHERE i.create_time IS NOT NULL AND i.type IS NOT NULL",
            "GROUP BY CONCAT(YEAR(i.create_time), '-Q', QUARTER(i.create_time)), i.type, d.dict_label",
            "ORDER BY timeKey"
    })
    List<SubstationStatVO> statByQuarter();

    // 按月
    @Select({
            "SELECT",
            "  DATE_FORMAT(i.create_time, '%Y-%m') AS timeKey,",
            "  i.type,",
            "  d.dict_label AS dictLabel,",
            "  COUNT(*) AS count",
            "FROM zk_substation_info i",
            "LEFT JOIN sys_dict_data d ON d.dict_value = i.type AND d.dict_type = 'substation_type'",
            "WHERE i.create_time IS NOT NULL AND i.type IS NOT NULL",
            "GROUP BY DATE_FORMAT(i.create_time, '%Y-%m'), i.type, d.dict_label",
            "ORDER BY timeKey"
    })
    List<SubstationStatVO> statByMonth();

    // 按周（周一为每周开始）
    @Select({
            "SELECT",
            "  CONCAT(YEAR(i.create_time), '-W', LPAD(WEEK(i.create_time, 1), 2, '0')) AS timeKey,",
            "  i.type,",
            "  d.dict_label AS dictLabel,",
            "  COUNT(*) AS count",
            "FROM zk_substation_info i",
            "LEFT JOIN sys_dict_data d ON d.dict_value = i.type AND d.dict_type = 'substation_type'",
            "WHERE i.create_time IS NOT NULL AND i.type IS NOT NULL",
            "GROUP BY CONCAT(YEAR(i.create_time), '-W', LPAD(WEEK(i.create_time, 1), 2, '0')), i.type, d.dict_label",
            "ORDER BY timeKey"
    })
    List<SubstationStatVO> statByWeek();


    @Select({
            "SELECT ",
            "    t.type,",
            "    COALESCE(dict.dict_label, '未知类型') AS dictLabel,",
            "    COALESCE(curr.count_current, 0) AS count,",
            "    ROUND(COALESCE(curr.count_current, 0) * 100.0 / NULLIF(total_all.total_count, 0), 2) AS percentage,",
            "    COALESCE(last.count_last, 0) AS lastYearCount,",
            "    CASE ",
            "        WHEN COALESCE(last.count_last, 0) = 0 THEN ",
            "            CASE WHEN COALESCE(curr.count_current, 0) > 0 THEN 100.0 ELSE 0.0 END",
            "        ELSE ",
            "            ROUND((COALESCE(curr.count_current, 0) - last.count_last) * 100.0 / last.count_last, 2)",
            "    END AS yoyGrowthPercent",
            "FROM (",
            "    SELECT '1' AS type UNION SELECT '2' UNION SELECT '3' UNION SELECT '4'",
            ") t",
            "LEFT JOIN (",
            "    SELECT type, COUNT(*) AS count_current",
            "    FROM zk_substation_info",
            "    WHERE type IN ('1','2','3','4')",
            "      AND create_time IS NOT NULL",
            "    GROUP BY type",
            ") curr ON t.type = curr.type",
            "LEFT JOIN (",
            "    SELECT type, COUNT(*) AS count_last",
            "    FROM zk_substation_info",
            "    WHERE type IN ('1','2','3','4')",
            "      AND create_time IS NOT NULL",
            "      AND YEAR(create_time) = YEAR(CURDATE()) - 1",
            "    GROUP BY type",
            ") last ON t.type = last.type",
            "LEFT JOIN sys_dict_data dict ",
            "    ON dict.dict_value = t.type AND dict.dict_type = 'substation_type'",
            "CROSS JOIN (",
            "    SELECT COUNT(*) AS total_count",
            "    FROM zk_substation_info",
            "    WHERE create_time IS NOT NULL AND type IS NOT NULL",
            ") total_all",
            "ORDER BY t.type"
    })
    List<SubstationTypeStatVO> statTypeDistributionAndYoY();

    /**
     * 新增变电站
     * @param substation
     * @return
     */
    int insert(Substation substation);

    /**
     * 修改变电站信息
     * @param substation
     * @return
     */
    int update(Substation substation);

    /**
     * 批量删除变电站信息
     * @param nums
     * @return
     */
    int removeBatch(@Param("nums") List<Long> nums);

    /**
     * 校验变电站是否存在
     * @param substation
     * @return
     */
    int checkSubstationExist(Substation substation);

    /**
     * 批量修改用户
     * @param substations
     * @return
     */
    int updateBatch(@Param("substations") List<Substation> substations);

    List<Map<String, Object>> selectLatest10Points();

    /**
     * 批量新增数据
     * @param substations
     * @return
     */
    int insertBatch(@Param("substations") List<Substation> substations);

    List<SubstationConnectionPointVO> selectListPageFold(@Param("substation") Substation substation,@Param("userId") Long userId);
}
